/**
 * Criacao: 28/09/2006
 * Autor: Vinicius Nunes Pinto 
 * 
 */

package testes.vSys.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;
import java.util.GregorianCalendar;
import java.util.List;

import testes.vSys.Constants;
import testes.vSys.bean.Color;
import testes.vSys.bean.Fuel;
import testes.vSys.bean.Location;
import testes.vSys.bean.Vehicle;
import testes.vSys.database.DatabaseConnection;
import testes.vSys.database.DatabaseController;

public class _hsql_VehicleDao implements IVehicleDao{

	/**
	 * Insere um novo veiculo na base de dados e retorna o veiculo inserido
	 * 
	 */
	
	static IModelDao modelDao;
	
	static {
		modelDao = DAOFactory.getModelDao();
	}
	
	public Vehicle createVehicle(Vehicle vehicle) {
		
		DatabaseConnection conn = null;
		
		String sql = "" +
		" INSERT INTO " +
		"	VS_VEHICLE ( " +
		" 		color, " +
		" 		year, " +
		"		plate, " +
		"		fuel_type, " +
		"		model, " +
		" 		vehicle_value, " +
		" 		state, " +
		" 		city " +
		"	) " +
		" 	VALUES( " +
		"		?, " +
		"		?, " +
		"		?, " +
		"		?, " +
		"		?, " +
		"		?, " +
		"		?, " +
		"		? " +
		"	) ";
		
		try{
			GregorianCalendar calendar = new GregorianCalendar();
			calendar.set(GregorianCalendar.YEAR,vehicle.getYear());
			
			conn = DatabaseController.getConnection();
			conn.executeUpdate(sql,new Object[]{
					vehicle.getColor().getId(),
					vehicle.getYear(),
					vehicle.getPlate(),
					vehicle.getFuel().getId(),
					vehicle.getModel().getId(), 
					vehicle.getValue(),
					vehicle.getLocation().getState(),
					vehicle.getLocation().getCity()
					} );
			
			ResultSet rs = conn.executeQuery("CALL IDENTITY();");
			
			
			if(rs.next()){
				vehicle.setId(rs.getInt(1));
				conn.close();
				return vehicle;
			}
		
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}finally {
			if (conn != null) {
				conn.close();
			}
		}
			
		return null;
	}

	/**
	 * Devolve uma collection com todas as cores
	 */
	public Collection<Color> getAvaiableColors() {
		DatabaseConnection conn = null;
		ResultSet rs = null;
		Collection<Color> colorCollection = new ArrayList<Color>();

		try{
			conn = DatabaseController.getConnection();
			String sql = "" +
				" SELECT " + 
				"	id_color, " +
				"	color_name " + 
				" FROM " +
				"	vs_color ";
			
			rs = conn.executeQuery(sql);
			
			while(rs.next()){
				Color color = new Color( (int) rs.getLong("id_color"), rs.getString("color_name"));
				colorCollection.add(color);
			}
			
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}finally {
			if (conn != null) {
				conn.close();
			}
		}
			return colorCollection;
	}

	/**
	 * Devolve uma colletion com todos os combustiveis
	 */
	public Collection<Fuel> getAvaiableFuels() {
		DatabaseConnection conn = null;
		ResultSet rs = null;
		Collection<Fuel> fuelCollection = new ArrayList<Fuel>();
		
		try{
			conn = DatabaseController.getConnection();
			String sql = "" +
				" SELECT " +
				"	id_fuel_type, " +
				"	fuel_type_name " + 
				" FROM " + 
				"	vs_fuel_type ";
			
			rs = conn.executeQuery(sql);
			
			while(rs.next()){
				Fuel fuel = new Fuel( (int) rs.getLong("id_fuel_type"), rs.getString("fuel_type_name"));
				fuelCollection.add(fuel);
			}
			
			
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}finally {
			if (conn != null) {
				conn.close();
			}
		}
		
		return fuelCollection;
	}

	/**
	 * Devolve o veiculo com id = id
	 */
	public Vehicle getVehicleById(int id) {
		DatabaseConnection conn = null;
		ResultSet rs = null;
		Vehicle vehicle = null;

		try{
			conn = DatabaseController.getConnection();
			String sql = "" +
				" SELECT " +
				"	* " +
				" FROM " +
				"	vs_vehicle v " +
				" WHERE " +
				"	v.id_vehicle = " + id + " ;";
			
			rs = conn.executeQuery(sql);
			
			while(rs.next()){
				vehicle = fillVehicle(rs);
			}
			
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}finally {
			if (conn != null) {
				conn.close();
			}
		}
		
		return vehicle;
	}
	
	private Vehicle fillVehicle(ResultSet rs){
		Vehicle vehicle = null;
		try{
			vehicle = new Vehicle();
			vehicle.setColor( getColorById( (int) (rs.getLong("color") ) ) );
			vehicle.setFuel( getFuelById( (int) rs.getLong("fuel_type") ) );
			vehicle.setId(rs.getInt("id_vehicle"));
			vehicle.setLocation(new Location(rs.getString("state"),rs.getString("city")));
			vehicle.setModel(modelDao.getModelById((int) (rs.getLong("model"))));
			vehicle.setPlate(rs.getString("plate"));
			vehicle.setValue(rs.getFloat("vehicle_value"));
			vehicle.setYear( rs.getInt("year"));
		}catch(Exception e){
			e.printStackTrace();
		}
		return vehicle;
	}

	public Vehicle getVehicleByPlate(String plate) {
		DatabaseConnection conn = null;
		ResultSet rs = null;
		Vehicle vehicle = null;

		try{
			conn = DatabaseController.getConnection();
			String sql = "" +
				" SELECT " +
				"	* " +
				" FROM " +
				"	vs_vehicle v " +
				" WHERE " +
				"	v.plate = '" + plate + "';"; 
			
			rs = conn.executeQuery(sql);
			
			if (rs.next()){
				vehicle = fillVehicle(rs);
			}
			
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}finally {
			if (conn != null) {
				conn.close();
			}
		}
		
		return vehicle;
	}

	public Collection<Vehicle> getVehicles() {
		DatabaseConnection conn = null;
		ResultSet rs = null;
		Collection<Vehicle> vehicle = new ArrayList<Vehicle>();

		try{
			conn = DatabaseController.getConnection();
			String sql = 
				" SELECT * " +
				" FROM " +
				" vs_vehicle v " +
				" ORDER BY id_vehicle";
							
			rs = conn.executeQuery(sql);
			
				while(rs.next()){
					vehicle.add(fillVehicle(rs));
				}
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}finally {
			if (conn != null) {
				conn.close();
			}
		}
			return vehicle;
	}
	
	public Collection<Vehicle> getVehicles( String optionOrderBy ) {
		DatabaseConnection conn = null;
		ResultSet rs = null;
		List<Vehicle> vehicle = new ArrayList<Vehicle>();

		try{
			conn = DatabaseController.getConnection();
			String sql = "" +
				" SELECT " +
				" v.* " +
				" FROM " +
				" vs_vehicle v , vs_model m, vs_brand b " +
				" WHERE " +
				"  v.model = m.id_model " + 
				"  AND m.brand = b.id_brand ";
			if(optionOrderBy != null && !optionOrderBy.equals(""))
				sql += "" +
				" ORDER BY " +
				optionOrderBy;
							
			rs = conn.executeQuery(sql);
			
				while(rs.next()){
					vehicle.add(fillVehicle(rs));
				}
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}finally {
			if (conn != null) {
				conn.close();
			}
		}
			return vehicle;
	}

	/**
	 * Deleta veiculo da base de dados
	 */
	public boolean removeVehicle(int vehicleId) {
		boolean result = false;
		DatabaseConnection conn = null;
        try {
            conn = DatabaseController.getConnection();

            String sql = "DELETE FROM vs_vehicle WHERE id_vehicle = ? ";
            int i = conn.executeUpdate(sql, new Object[] { vehicleId });

            // assumindo que � chave (volta apenas 1)
            if (i == 1){
            	result = true;
            }
            
        } catch (Exception e) {
        	e.printStackTrace();
        } finally {
			if (conn != null) {
				conn.close();
			}
		}
	       return result;
	}


	/**
	 * Altera veiculo na base de dados
	 */
	public boolean updateVehicle(Vehicle vehicle) {
		boolean result = false;
		DatabaseConnection conn = null;

		if (vehicle == null) return false;

        try {
            conn = DatabaseController.getConnection();
            String sql = "" +
            	"UPDATE " +
            	"	vs_vehicle " +
            	"SET " +
            	"	color = ?, " +
            	"   year = ?, " +
            	"   plate = ?, " +
            	"	fuel_type = ?, " +
            	"   model = ?, " +
            	"   vehicle_value = ?, " + 
            	"   state = ?, " +
            	"   city = ? " +
            	" WHERE " +
            	"	id_vehicle = ? ";

            int i = conn.executeUpdate(sql, new Object[] {	
            		vehicle.getColor().getId(),
            		vehicle.getYear(),
            		vehicle.getPlate(),
            		vehicle.getFuel().getId(),
            		vehicle.getModel().getId(),
            		vehicle.getValue(),
            		vehicle.getLocation().getState(),
            		vehicle.getLocation().getCity(),
            		vehicle.getId()
            	} );

            // assumindo que � chave (volta apenas 1)
            if (i == 1){
            	result = true;
            }
            
        } catch (Exception e) {
        	e.printStackTrace();
		}finally {
			if (conn != null) {
				conn.close();
			}
		}
			return result;
	}

	/**
	 * Devolve a cor com o id = colorId
	 */
	public Color getColorById(int colorId) {
		DatabaseConnection conn = null;
		ResultSet rs = null;
		Color color = null;

		try{
			conn = DatabaseController.getConnection();
			String sql = "" +
				" SELECT " + 
				"	id_color, " +
				"	color_name " + 
				" FROM " +
				"	vs_color " +
				" WHERE " +
				"	id_color = " + colorId;

			rs = conn.executeQuery(sql);
			rs.next();
			
			color = new Color((int)(rs.getLong("id_color")), rs.getString("color_name"));
			
			
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}finally {
			if (conn != null) {
				conn.close();
			}
		}
			return color;
	}

	/**
	 * Devolve o combustivel com id = fuelId
	 */
	public Fuel getFuelById(int fuelId) {
		DatabaseConnection conn = null;
		ResultSet rs = null;
		Fuel fuel = null;
		
		try{
			conn = DatabaseController.getConnection();
			String sql = "" +
				" SELECT " +
				"	id_fuel_type, " +
				"	fuel_type_name " + 
				" FROM " + 
				"	vs_fuel_type " +
				" WHERE " +
				"	id_fuel_type = " + fuelId;
			
			rs = conn.executeQuery(sql);
			rs.next();
			
			fuel = new Fuel((int)(rs.getLong("id_fuel_type")), rs.getString("fuel_type_name"));
			
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}finally {
			if (conn != null) {
				conn.close();
			}
		}
			return fuel;
	}

	public Collection<Vehicle> getVehicles(Vehicle vehicle) {
	      return null;
	}
 
		
	public Collection<Vehicle> getVehicle(String vehicleId, String plate, String initYear, String endYear,  
            String initValue, String endValue, String color, String model, String brand, String fuel, String city, String state) {

      DatabaseConnection conn = null;
      ResultSet rs = null;
      List<Vehicle> vehicles = new ArrayList<Vehicle>();
      
      String sqlWhere = " WHERE vehicle.model = model.id_model";
      List<Object> whereObject = new ArrayList<Object>();
      
      if (!vehicleId.equals("")) {
          sqlWhere+=" AND vehicle.vehicleId = ?";
          whereObject.add(vehicleId);
      }         
      if (!plate.equals("")) {
          sqlWhere+=" AND vehicle.plate = ?";
          whereObject.add(plate);
      }        
      if (!initYear.equals("")) {
          sqlWhere+=" AND vehicle.year >= ?";
          whereObject.add(initYear);
      }        
      if (!endYear.equals("")) {
          sqlWhere+=" AND vehicle.year <= ?";
          whereObject.add(endYear);
      }        
      if (!initValue.equals("")) {
          sqlWhere+=" AND vehicle.value >= ?";
          whereObject.add (initValue);
      }
      if (!endValue.equals("")) {
          sqlWhere+=" AND vehicle.value <= ?";
          whereObject.add(endValue);
      }
      if (!color.equals("")) {
          sqlWhere+=" AND vehicle.color = ?";
          whereObject.add(color);
      }
      if (!model.equals("")) {
          sqlWhere+=" AND vehicle.model = ?";
          whereObject.add(model);
      }
      if (!brand.equals("")) {
          sqlWhere+=" AND model.brand = ?";
          whereObject.add(brand);
      }
      if (!fuel.equals("")) {
          sqlWhere+=" AND vehicle.fuel_type = ?";
          whereObject.add(fuel);
      }
      if (!city.equals("")) {
          sqlWhere+=" AND vehicle.city = ?";
          whereObject.add(city);
      }
      if (!state.equals("")) {
          sqlWhere+=" AND vehicle.state = ?";
          whereObject.add(state);
      }
      
      try {
          conn = DatabaseController.getConnection();
          
          String sql = "SELECT * FROM vs_vehicle vehicle, vs_model model" + sqlWhere;
          rs = conn.executeQuery (sql, (Object[]) whereObject.toArray(new Object[whereObject.size()]));
          
          while (rs.next()) {
              vehicles.add(fillVehicle(rs));
          }
          
      } catch (Exception e) {
          e.printStackTrace();
      }finally {
			if (conn != null) {
				conn.close();
			}
		}
         
      return vehicles;
  }
}
